MySQL面经[上]

SQL调优及索引失效

索引失效的几种情况

1. MySQL 可以为多个字段创建索引，一个索引可以包括 16 个字段。对于多列索引，过滤条件要使用索引，必须按照索引建立的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。如果查询条件中没有使用这些字段中第 1 个字段时，多列（或联合）索引不会被使用。

2. 计算、函数、类型转换（自动或手动）导致索引失效

3. 范围条件右边的列索引失效

应用开发中范围查询，例如：金额查询，日期查询往往都是范围查询。应将查询条件放置 WHERE 语句最后。（创建的联合索引中，务必把范围涉及到的字段写在最后）

4. 不等于（ != 或者 <> ）索引失效

5.is null 可以使用索引，is not null 无法使用索引

最好在设计数据表的时候就将字段设置为 NOT NULL 约束，比如可以将 INT 类型的字段，默认值设置为 0。将字符类型的默认值设置为空字符串 '' 。同理，在查询中使用 not like 也无法使用索引，导致全表扫描

6. like 以通配符 % 开头索引失效

Alibaba《Java开发手册》，强制要求：页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决

7. OR 前后存在非索引的列，索引失效

8. 数据表和表的字符集统一使用 utf8mb4

统一使用 utf8mb4 （5.5.3版本以上支持）兼容性更好，统一字符集可以避免由于字符集转换产生的乱码。不同的字符集进行比较前需要进行转换会造成索引失效。

SQL调优的方法

1.尽量不要使用select * ,只查需要的字段

2.在where和order by 的字段建立索引(索引数量不要太多,索引降低了insert和update的效率)

3.调整where的条件顺序，过滤数量多的条件放在前面

4.尽量把多句sql用一条查出来

5.尽量把条件放到where中而不是having中,因为where在聚合前就筛选了记录,having是检出所有记录后再去过滤

6.使用表的别名,这样就不需要去寻找是哪个表的字段

7.如果能确定两张表没有重复的数据,就用union all代替union。因为union会尝试合并两张表，在输出前进行排序

8.对于连续的，用between而不用in

9.update时只更新必要的字段

10.用varchar代替char，varchar是可变长度，char是定长

11.最左前缀索引：like只用于'string%'，语句中的=和in会动态调整顺序

12.唯一索引：唯一键区分度在0.1以上

13.联合索引：避免select * ，查询列使用覆盖索引

sql

SELECT uid From user Where gid = 2 order by ctime asc limit 10ALTER TABLE user add inde

14.语句优化

①char固定长度查询效率高，varchar第一个字节记录数据长度

②应该针对Explain中Rows增加索引

③group/order by字段均会涉及索引

④Limit中分页查询会随着start值增大而变缓慢，通过子查询+表连接解决

select * from mytbl order by id limit 100000,10  改进后的SQL语句如下：select * from mytbl where id >= ( select id from mytbl order by id limit 100000,1 ) limit 10select * from mytbl inner ori join (select id from mytbl order by id limit 100000,10) as tmp on tmp.id=ori.id;

⑤count会进行全表扫描，如果估算可以使用explain

⑥delete删除表时会增加大量undo和redo日志，确定删除可使用trancate

表结构优化:

①单库不超过200张表

②单表不超过500w数据

③单表不超过40列

④单表索引不超过5个

数据库范式

①第一范式（1NF）列不可分割

②第二范式（2NF）属性完全依赖于主键 [ 消除部分子函数依赖 ]

③第三范式（3NF）属性不依赖于其它非主属性 [ 消除传递依赖 ]

配置优化：

配置连接数、禁用Swap、增加内存、升级SSD硬盘

MySQL中的SQL规范

始终使用EXPLAIN语句

开发 SQL 查询时，养成使用 EXPLAIN 的习惯至关重要。特别要注意索引的使用效率。例如：

sql

EXPLAIN SELECT * FROM employees WHERE department = 'Engineering';

“EXPLAIN”语句的输出将提供 MySQL 打算如何执行查询的详细信息，包括是否使用索引和操作顺序。输出可能如下所示：

sql

+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
| id | selecttype | table     | type | possiblekeys | key  | keylen | ref  | rows | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
| 1  | SIMPLE      | employees | ref  | department    | department| 14      | const| 100  | Using index |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+

执行 DELETE/UPDATE 时包含 LIMIT

执行 DELETE 或 UPDATE 语句时，建议添加 LIMIT 语句。示例如下：

sql

sql复制代码-- Delete only one row from the 'employees' table with a specific condition
DELETE FROM employees WHERE department = 'Engineering' LIMIT 100;

添加 LIMIT 子句有助于减少错误 SQL 语句的影响。如果在命令行中执行 SQL 命令时忘记添加 LIMIT，可能会不小心删除所有数据。
通过添加例如 LIMIT 200，可以将潜在的损害限制在一定范围内，以便在意外删除时通过 binlog 日志恢复最多 200 条记录。
在 SQL 查询中使用 LIMIT 1 可以提高效率，因为它在找到第一个匹配项后停止查询执行，避免了不必要的表扫描。
此外，包含 LIMIT 子句有助于避免在 DELETE 等操作期间的长时间事务，特别是在处理大型数据集时，可以防止 CPU 过载和性能下降。

表和字段一定要添加适当的注释

设计数据库表时，养成为所有表和字段添加适当注释的习惯。这种做法有助于未来维护，为数据库结构提供清晰的上下文。

示例如下:

sql

CREATE TABLE `account` (
  `id` int(11) NOT NULL AUTOINCREMENT COMMENT '主键',
  `name` varchar(255) DEFAULT NULL COMMENT '账户名称',
  `balance` int(11) DEFAULT NULL COMMENT '账户余额',
  `createtime` datetime NOT NULL COMMENT '创建时间',
  `updatetime` datetime NOT NULL ON UPDATE CURRENTTIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idxname` (`name`) USING BTREE
) ENGINE=InnoDB AUTOINCREMENT=1570068 DEFAULT CHARSET=utf8 ROWFORMAT=REDUNDANT COMMENT='账户信息表';

关键字大写并使用缩进

SQL 书写规范中强调关键字大写和使用缩进，是为了提升代码的可读性和整洁度。关键字大写使得关键词更加醒目，便于快速识别，而缩进则清晰展现了语句结构，有助于理解代码逻辑，两者结合极大地增强了代码的可维护性和团队协作效率。

正例：

sql

SELECT stu.name, SUM(stu.score)
FROM Student AS stu
WHERE stu.classNo = '10'
GROUP BY stu.name;

反例

sql

SELECT stu.name, sum(stu.score) from Student stu WHERE stu.classNo = '10' group by stu.name.

在 INSERT 语句中指定字段名

这样做可以确保数据插入的准确性和健壮性。明确指定字段名能避免因表结构变动引发的错误，使得插入操作更加明确且易于维护，尤其是在表结构频繁调整的场景下。此外，这种方法还能提升代码的可读性，便于其他开发者理解插入操作的具体意图，以及在出现问题时快速定位和调试。

sql

INSERT INTO Student (id, name, score) VALUES (123, 'user1', 100);

索引的命名规范

统一命名规范能让索引更易读、易懂，方便团队协作和维护，减少操作错误，利于自动化脚本处理，以及符合标准化要求，从而提升数据库的管理效率和系统稳定性。

对于主键索引，使用“pk”作为前缀后跟字段名，如“pk_id”表示“id”字段的主键索引，清晰表明此索引代表主键约束。
对于唯一索引，使用“uk”作为前缀后跟字段名，如“uk_email”表示“email”字段的唯一索引，表示此索引强制字段上的唯一约束。
对于常规索引，使用“idx”作为前缀后跟字段名，如“idx_lastname”表示“lastname”字段的索引，用于优化指定字段的搜索。

用过 explain 吗？说说怎么分析的？

你好面试官，我当然用过 explain，我平时都会那它去查看 SQL 语句是否还能优化。接下来我从主要属性跟实际例子来讲解：

主要的属性

1） 🌱 id

查询中每个 SELECT 子句的标识符。简单查询的 id 通常为 1，复杂查询（如包含子查询或 UNION）的 id 会有多个。

2） 🌱 select_type

描述查询的类型。比如：简单查询显示为 SIMPLE，子查询显示为 SUBQUERY，UNION 中的第二个和后续查询显示为 UNION。

3）🌱 table

表名称这个就不用再详细解释了吧哈哈。

4） 🌱 partitions

表示查询涉及到的分区。如果你有使用分区表的话才需要关注此字段。

5） 🌱 type（重点记忆⭐）

表示访问的类型，这里也可以看出你的 SQL 的性能。可能的值从最好到最差包括：system、const、eq_ref、ref、range、index、ALL。其中 ALL 表示全表扫描，效率最低。

system：
表示查询的表只有一行（系统表）。这是一个特殊的情况，不常见。
const：
表示查询的表最多只有一行匹配结果。这通常发生在查询条件是主键或唯一索引，并且是常量比较，以下是一个使用主键查找的例子：
sql
```
EXPLAIN SELECT * FROM employees WHERE employee_id = 12345;
```
eq_ref：
表示对于每个来自前一张表的行，MySQL 仅访问一次这个表。这通常发生在连接查询中使用主键或唯一索引的情况下，例子如下：
sql
```
EXPLAIN SELECT * FROM employees e JOIN departments d ON e.department_id = d.department_id;
```
ref：
MySQL 使用非唯一索引扫描来查找行。查询条件使用的索引是非唯一的（如普通索引），例子如下使用了非唯一索引进行查找：
sql
```
EXPLAIN SELECT * FROM employees WHERE department_id = 5;
```
range：表示 MySQL 会扫描表的一部分，而不是全部行。范围扫描通常出现在使用索引的范围查询中（如 BETWEEN、>, <, >=, <=）。下面是范围查询：
sql
```
EXPLAIN SELECT * FROM employees WHERE salary BETWEEN 50000 AND 100000;
```
index：表示 MySQL 扫描索引中的所有行，而不是表中的所有行。即使索引列的值覆盖查询，也需要扫描整个索引。以下是使用索引扫描例子：
sql
```
EXPLAIN SELECT name FROM employees;
```
all（性能最差）：表示 MySQL 需要扫描表中的所有行，即全表扫描。这通常出现在没有索引的查询条件中。以下是全表扫描例子：
sql
```
EXPLAIN SELECT * FROM employees;
```

6） 🌱 possible_keys

表示查询可能使用的索引列表。

7） 🌱 key

实际使用索引。如果没有使用索引，该字段显示为 NULL。

8） 🌱 key_len

这个字段表示使用的索引的长度。该值是根据索引的定义和查询条件计算的。

9） 🌱 rows

MySQL 会估计为了找到所需的行，需要读取的行数。该值是一个估计值，不是精确值。

10）🌱 filtered

显示查询条件过滤掉的行的百分比。一个高百分比表示查询条件的选择性好。

11）🌱 Extra

额外信息，如 Using index（表示使用覆盖索引）、Using where（表示使用 WHERE 条件进行过滤）、Using temporary（表示使用临时表）、Using filesort（表示需要额外的排序步骤）。

实际例子

1.创建 employees 表

sql

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    salary DECIMAL(10, 2),
    hire_date DATE,
    INDEX (department_id)
);

我们要执行以下查询来查找部门 ID 为 5 且薪水在 50000 到 100000 之间的员工，并按薪水降序排序：

sql

SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE department_id = 5 AND salary BETWEEN 50000 AND 100000
ORDER BY salary DESC;

2.我们先使用 explain 分析计划进行分析：

sql

EXPLAIN SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE department_id = 5 AND salary BETWEEN 50000 AND 100000
ORDER BY salary DESC;

输出结果如下：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	employees	NULL	ref	department_id	department_id	4	const	5000	20.00	Using where; Using filesort

3.分析执行计划

从执行计划中看出，type 为 ref，表示使用了 department_id 索引，这是个非唯一索引。key 为 department_id 这个索引，而且 rows 为 5000，表示扫描了 5000 行匹配的 department_id = 5 的条件。从 Extra 看出在应用 WHERE 条件后，还需要进行文件排序来满足 ORDER BY 子句。

4.找出问题

尽管查询使用了索引，但由于索引不完全覆盖查询的条件和排序，查询需要进行额外的文件排序。这可能会导致性能瓶颈，特别是在结果集较大时。

5.优化解决它！

创建复合索引

创建一个包含 department_id 和 salary 的复合索引，这样可以覆盖查询的 WHERE 和 ORDER BY 条件：

sql

CREATE INDEX idx_department_salary ON employees (department_id, salary);

复合索引可以使查询在扫描 department_id 列时，同时按 salary 列排序，避免额外的文件排序。

再次执行计划分析

优化后的 EXPLAIN 输出如下：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	employees	NULL	range	idx_department_salary	idx_department_salary	5	NULL	500	100.00	Using where

6.分析优化后的结果

从新的 EXPLAIN 输出中可以看出：

type: range，表示使用范围扫描，这是个相对高效的访问类型。
key: idx_department_salary，表示实际使用了复合索引。
rows: 500，估计读取的行数减少了，因为索引更精确地覆盖了查询条件。
Extra: 仅显示 Using where，不再需要文件排序，因为索引已经覆盖了排序需求。

SQL总体优化原则

创建合适的索引
减少不必要访问的列
使用覆盖索引
语句改写
数据结转
选择合适的列进行排序
适当的列冗余
SQL拆分
适当应用ES

事务4大特性

**事务4大特性：**原子性、一致性、隔离性、持久性

原⼦性： 事务是最⼩的执⾏单位，不允许分割。事务的原⼦性确保动作要么全部完成，要么全不执行

一致性： 执⾏事务前后，数据保持⼀致，多个事务对同⼀个数据读取的结果是相同的；

隔离性： 并发访问数据库时，⼀个⽤户的事务不被其他事务所⼲扰，各并发事务之间数据库是独⽴的；

持久性： ⼀个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发⽣故障也不应该对其有任何影响。

实现保证：

MySQL的存储引擎InnoDB使用重做日志保证一致性与持久性，回滚日志保证原子性，使用各种锁来保证隔离性。

事务的隔离级别

**读未提交：**最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。

**读已提交：**允许读取并发事务已经提交的数据，可以阻⽌脏读，但是幻读或不可重复读仍有可能发⽣。

**可重复读：**同⼀字段的多次读取结果都是⼀致的，除⾮数据是被本身事务⾃⼰所修改，可以阻⽌脏读和不可重复读，会有幻读。[MySQL的默认隔离级别]

**串行化：**最⾼的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执⾏，这样事务之间就完全不可能产⽣⼲扰。

默认隔离级别

**默认隔离级别：**可重复读；

同⼀字段的多次读取结果都是⼀致的，除⾮数据是被本身事务⾃⼰所修改；

可重复读是有可能出现幻读的，如果要保证绝对的安全只能把隔离级别设置成SERIALIZABLE；这样所有事务都只能顺序执行，自然不会因为并发有什么影响了，但是性能会下降许多。

第二种方式，使用MVCC解决快照读幻读问题（如简单select），读取的不是最新的数据。维护一个字段作为version，这样可以控制到每次只能有一个人更新一个版本。

select id from table_xx where id = ? and version = Vupdate id from table_xx where id = ? and version = V+1

第三种方式，如果需要读最新的数据，可以通过GapLock+Next-KeyLock可以解决当前读幻读问题，

select id from table_xx where id > 100 for update;select id from table_xx where id > 100 lock in share mode;

RR和RC使用场景

事务隔离级别RC(read commit)和RR（repeatable read）两种事务隔离级别基于多版本并发控制MVCC(multi-version concurrency control）来实现。

行锁，表锁，意向锁

InnoDB⽀持⾏级锁(row-level locking)和表级锁,默认为⾏级锁

InnoDB按照不同的分类的锁：

共享/排它锁(Shared and Exclusive Locks)：行级别锁，

意向锁(Intention Locks)，表级别锁

间隙锁(Gap Locks)，锁定一个区间

记录锁(Record Locks)，锁定一个行记录

表级锁：（串行化）

Mysql中锁定粒度最大的一种锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM和 InnoDB引擎都支持表级锁。

行级锁：（RR、RC）

Mysql中锁定粒度最小的一种锁，只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。 InnoDB支持的行级锁，包括如下几种：

记录锁（Record Lock）: 对索引项加锁，锁定符合条件的行。其他事务不能修改和删除加锁项；

间隙锁（Gap Lock）: 对索引项之间的“间隙”加锁，锁定记录的范围，不包含索引项本身，其他事务不能在锁范围内插入数据。

Next-key Lock： 锁定索引项本身和索引范围。即Record Lock和Gap Lock的结合。可解决幻读问题。

InnoDB 支持多粒度锁（multiple granularity locking），它允许行级锁与表级锁共存，而意向锁就是其中的一种表锁。

共享锁（ shared lock, S ）锁允许持有锁读取行的事务。加锁时将自己和子节点全加S锁，父节点直到表头全加IS锁

排他锁（ exclusive lock， X ）锁允许持有锁修改行的事务。加锁时将自己和子节点全加X锁，父节点直到表头全加IX锁

意向共享锁（intention shared lock, IS）：事务有意向对表中的某些行加共享锁（S锁）

意向排他锁（intention exclusive lock, IX）：事务有意向对表中的某些行加排他锁（X锁）

[幻读](Phantom Read）是指当事务不是独立执行时发生的一种现象**。具体来说，幻读是指在同一个事务中，前后两次查询相同范围的时候，由于其他事务的插入或删除操作导致得到的结果不一致

MVCC多版本并发控制

MVCC是一种多版本并发控制机制，通过事务的可见性看到自己预期的数据，能降低其系统开销。（RC和RR级别工作）

InnoDB的MVCC,是通过在每行记录后面保存系统版本号(可以理解为事务的ID)，每开始一个新的事务，系统版本号就会自动递增，事务开始时刻的系统版本号会作为事务的ID。这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的，防止幻读的产生。

1.MVCC手段只适用于Msyql隔离级别中的读已提交（Read committed）和可重复读（Repeatable Read）.

2.Read uncimmitted由于存在脏读，即能读到未提交事务的数据行，所以不适用MVCC.

3.简单的select快照度不会加锁，删改及select for update等需要当前读的场景会加锁

原因是MVCC的创建版本和删除版本只要在事务提交后才会产生。客观上，mysql使用的是乐观锁的一整实现方式，就是每行都有版本号，保存时根据版本号决定是否成功。Innodb的MVCC使用到的快照存储在Undo日志中，该日志通过回滚指针把一个数据行所有快照连接起来。

版本链

在InnoDB引擎表中，它的聚簇索引记录中有两个必要的隐藏列：

trx_id

这个id用来存储的每次对某条聚簇索引记录进行修改的时候的事务id。

roll_pointer

每次对哪条聚簇索引记录有修改的时候，都会把老版本写入undo日志中。这个roll_pointer就是存了一个指针，它指向这条聚簇索引记录的上一个版本的位置，通过它来获得上一个版本的记录信息。(注意插入操作的undo日志没有这个属性，因为它没有老版本)

每次修改都会在版本链中记录。SELECT可以去版本链中拿记录，这就实现了读-写，写-读的并发执行，提升了系统的性能。

MySQL面经[上] ​

SQL调优及索引失效 ​

索引失效的几种情况 ​

SQL调优的方法 ​

MySQL中的SQL规范 ​

始终使用EXPLAIN语句 ​

执行 DELETE/UPDATE 时包含 LIMIT ​

表和字段一定要添加适当的注释 ​

关键字大写并使用缩进 ​

在 INSERT 语句中指定字段名 ​

索引的命名规范 ​

用过 explain 吗？说说怎么分析的？ ​

主要的属性 ​

1） 🌱 id ​

2） 🌱 select_type ​

3）🌱 table ​

4） 🌱 partitions ​

5） 🌱 type（重点记忆⭐） ​

6） 🌱 possible_keys ​

7） 🌱 key ​

8） 🌱 key_len ​

9） 🌱 rows ​

10）🌱 filtered ​

11）🌱 Extra ​

实际例子 ​

1.创建 employees 表 ​

2.我们先使用 explain 分析计划进行分析： ​

3.分析执行计划 ​

4.找出问题 ​

5.优化解决它！ ​

6.分析优化后的结果 ​

SQL总体优化原则 ​

事务4大特性 ​

事务的隔离级别 ​

默认隔离级别 ​

RR和RC使用场景 ​

行锁，表锁，意向锁 ​

MVCC多版本并发控制 ​

版本链 ​